<?php
/*
* 大客户经理数据模块
* @author  chendm<402197974@qq.com> 
* @date  2017-08-31 16:53:00
* 
*/
class  Manager_model extends CI_Model
{
	public function __construct()
    {
        $this->load->database();
        $this->load->model('users_model');
        $this->load->helper("url");
    }

    // api
    public function manager_higher_info()
    {
        $sql = "SELECT
  m1.*,
  m2.name     AS parentName,
  m2.phone    AS parentPhone,
  m2.identity AS parentIdentity
FROM

  (SELECT
     m.id,
     m.tel        AS username,
     m.name       AS name,
     m.identity,
     m.phone,
     m.email,
     mb.parent_id AS parentId
   FROM manager m LEFT JOIN manager_bind mb ON m.id = mb.manager_id) AS m1 LEFT JOIN manager AS m2
    ON m1.parentId = m2.id
";
        $query = $this->db->query($sql);
        $manager_list =$query->result_array();
        return $manager_list;

    }

    //获取某个大区经理下的大客户经理列表
    public function getManagerList($manager_id,$status = 0,$sort_type = 1,$asc_type = '',$limit = 3,$page = 1,$search = ''){

	    // 修改为大区经理直招的 cj
        // SELECT manager_id FROM manager_bind_history WHERE parent_id=mb.manager_id OR manager_id=mb.manager_id 改为 mb.manager_id
        $sql = "
                SELECT m.id,mb.parent_id,m.name,m.tel,m.pic_path,m.manager_status,m.name,m.tel,
                    (SELECT IFNULL(SUM(order_money),0)  FROM customer_order co WHERE co.manager_id IN(mb.manager_id) 
                    AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time) order_total_money,
                    (SELECT COUNT(1) FROM manager_bind_history  WHERE parent_id=mb.manager_id AND date_format(start_time,'%Y-%m')=date_format(now(),'%Y-%m') AND end_time = '2099-12-31 23:59:59' ) recruit_count_month,
                    (SELECT COUNT(1) FROM manager_bind_history  WHERE parent_id=mb.manager_id AND end_time = '2099-12-31 23:59:59' ) recruit_count_all,
                    (SELECT start_time FROM manager_bind_history  mbh WHERE mbh.manager_id=mb.manager_id AND end_time = '2099-12-31 23:59:59'
		ORDER BY id DESC LIMIT 0,1) rel_time
                FROM manager_bind  mb
                LEFT JOIN manager_bind_history mbh ON mb.manager_id = mbh.manager_id
                LEFT JOIN manager m ON m.id=mb.manager_id
                WHERE m.is_deleted=0 and mb.parent_id = $manager_id";
        //按状态筛选数据
        switch ($status)
        {
            case 1:
                $sql.=" AND manager_status='正常状态'";
                break;
            case 2:
                $sql.=" AND manager_status='已离职'";
                break;
            case 3:
                $sql.=" AND manager_status='培训中'";
                break;
            default:
        }
        //按输入手机号、姓名模糊匹配查询
        if($search){
            $sql.=" AND (name LIKE '%$search%' OR tel LIKE '%$search%')";
        }
        $sql.=" GROUP BY m.id";

        //按照选择的方式排序
        switch ($sort_type)
        {
            case 1:
                $sql.=" ORDER BY id";
                break;
            case 2:
                $sql.=" ORDER BY recruit_count_all";
                break;
            case 3:
                $sql.=" ORDER BY order_total_money";
                break;
            case 4:
                $sql.=" ORDER BY rel_time";
                break;

        }
        //升序、降序
        if ($asc_type){
            $sql.=" ASC";
        }else{
            $sql.=" DESC";
        }
        $limit_from = ($page-1)*$limit;
        $sql.=" LIMIT ".$limit_from.",".$limit;

        // echo $sql;exit;
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            foreach ($rs as &$v){
                //头像
                $v['pic_path'] = $v['pic_path']?$this->config->item("upload_url").$v['pic_path']:"";
            }
            // echo '<pre/>';print_r($rs);exit;
            return $rs;
        }else{
            return [];
        }
       
    }

    //获取某个大区经理下的大客户经理销售榜
    public function getManagerTop($manager_id, $type = 'all',$limit = 3){
        //统计成员销售总额并按照销售业绩排序
        switch ($type){
            case 'season':
                // bf cj
                $getMonthDays = date("t",mktime(0, 0, 0,date('n')+(date('n')-1)%3,1,date("Y")));
                $start = date('Y-m-d H:i:s', mktime(0, 0, 0,date('n')-(date('n')-1)%3,1,date('Y')));
                $end = date('Y-m-d H:i:s', mktime(23,59,59,date('n')+(date('n')-1)%3,$getMonthDays,date('Y')));
//                $start =  date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
//                $end =  date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));
                break;
            case 'month':
                $start =  date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
                $end =  date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));
                break;
            default:
                break;
        }

        if ($start && $end){
            $where =  " AND co.payment_date BETWEEN '".$start."' and '".$end."'";
        }
        // $sql = "  SELECT manager.id,manager.name,ifnull(sum(customer_order.order_money),0) order_money FROM manager LEFT JOIN customer_order 
        //     ON manager.id = customer_order.manager_id WHERE 1=1 $where 
        //     AND customer_order.manager_id IN (
        //                 SELECT manager_id FROM manager_bind_history 
        //                 WHERE parent_id IN ($ids) AND start_time <= customer_order.completed_date AND end_time >= customer_order.completed_date
        //         ) 
        //     AND customer_order.order_status = '交易成功'
        //     AND manager.id IN (
        //                 SELECT manager_id FROM manager_bind_history WHERE parent_id IN ($ids)
        //     )  
        //     GROUP BY manager.id 
        //     ORDER BY sum(customer_order.order_money) DESC 
        //     LIMIT 0,$limit";

        // 修改为大区经理直招的 cj
        // SELECT manager_id FROM manager_bind_history WHERE parent_id=mb.manager_id OR manager_id=mb.manager_id 改为 mb.manager_id
         $sql = "
                SELECT m.id,mb.parent_id,m.name,
                    (SELECT IFNULL(SUM(order_money),0)  FROM customer_order co WHERE co.manager_id IN(mb.manager_id) 
                    AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time $where) order_total_money,
                    (SELECT COUNT(1) FROM manager_bind_history  WHERE parent_id=mb.manager_id) recruit_count
                FROM manager_bind  mb
                LEFT JOIN manager_bind_history mbh ON mb.manager_id = mbh.manager_id
                LEFT JOIN manager m ON m.id=mb.manager_id  
                WHERE mb.parent_id = $manager_id AND m.is_deleted=0 
                ORDER BY order_total_money DESC 
                LIMIT 0,$limit
        ";
        // echo $sql;exit;
        // echo '<pre/>';print_r($rs);exit;
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            return $rs;
        }else{
            return false;
        }
    }

    //更新大客户经理的状态
    public function updateStatus($id,$status){
        $bool = $this->db->update("manager",array('manager_status'=>$status),'id='.$id);
        return $this->db->affected_rows();
    }

    //获取大客户经理详情数据
    public function  getManagerDetail($id,$pid=0,$page=0,$limit=0){
        $sql =  "
                SELECT m.id,m.name,m.gender,m.tel,m.email,m.pic_path,m.manager_status,
                    (SELECT COUNT(1)  FROM customer_order co 
                    LEFT JOIN manager_bind_history mbh ON mbh.manager_id = co.manager_id
                    WHERE co.manager_id IN(SELECT manager_id FROM manager_bind_history WHERE manager_id=m.id) 
                    AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time) order_count,
                    (SELECT IFNULL(SUM(order_money),0)  FROM customer_order co 
                    LEFT JOIN manager_bind_history mbh ON mbh.manager_id = co.manager_id
                    WHERE co.manager_id IN(SELECT manager_id FROM manager_bind_history WHERE manager_id=m.id) 
                    AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time) order_total_money,
                    (SELECT COUNT(1) FROM manager_bind_history  WHERE parent_id=m.id) recruit_count_all,
                    (SELECT start_time FROM manager_bind_history  mbh WHERE mbh.manager_id=m.id ORDER BY end_time  DESC LIMIT 0,1) rel_time
                FROM manager  m
                WHERE m.id = $id and m.is_deleted=0
        ";
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            foreach ($rs as &$v){
                //头像
                $v['pic_path'] = $v['pic_path']?$this->config->item("upload_url").$v['pic_path']:"";
            }
            //获取业绩指标
            $sql = "
                    SELECT n.id,n.performance,n.recruit,n.date
                    FROM norm n 
                    WHERE manager_id = $id
                    AND n.date>=date_format(now(),'%Y-%m')
                    ORDER BY n.date ASC
            ";

            $norm_rs = $this->db->query($sql)->result_array();

            //业绩指标数据绑定
            $rs[0]['norminfo']['performance'] = '0.00';
            $rs[0]['norminfo']['recruit'] = 0;
            $rs[0]['norminfo']['next_performance'] = '0.00';
            $rs[0]['norminfo']['next_recruit'] = 0;
            
            foreach ($norm_rs as $val) {
                //本月
                if( date('Y-m', strtotime($val['date'])) == date('Y-m')){
                    $rs[0]['norminfo']['performance'] = $val['performance'];
                    $rs[0]['norminfo']['recruit'] = $val['recruit'];
                }

                //下月
                if( date('Y-m', strtotime($val['date'])) == date('Y-m', strtotime("+1 month")) ){
                    $rs[0]['norminfo']['next_performance'] = $val['performance'];
                    $rs[0]['norminfo']['next_recruit'] = $val['recruit'];
                }
            }
            
            
            //数据跟踪
            $rs[0]['statistics'] = $this->getStatistics($id,date('Y-m'));

            $record = $this->users_model->get_manager_record($id,$pid,$page,$limit);
            $rs[0]['recordlist'] = $record;
            // echo '<pre/>';print_r($rs);exit;
            return $rs[0];
        }else{
            return false;
        }
    }

    //更新指标
    public function updateNorm($manager_id,$date,$performance,$recruit){
        //查询大客户经理本月业绩数据
        $rs = $this->db->where(array('manager_id'=>$manager_id,'date'=>$date))->get("norm")->result_array();
        // echo '<pre/>';print_r($rs);exit;
        if($rs){
          $this->db->update("norm",array('performance'=>$performance,'recruit'=>$recruit),'id='.$rs[0]['id']);
        }else{
          $insertdata = array(
            'manager_id' => $manager_id,
            'performance' => $performance,
            'recruit' => $recruit,
            'date' => $date
          );
          $this->db->insert("norm",$insertdata);
        }
        return $this->db->affected_rows();
    }

    public function getStatistics($manager_id,$date){
         $sql =  "
                SELECT 
                    (SELECT COUNT(1)  FROM customer_order co 
                    LEFT JOIN manager_bind_history mbh ON mbh.manager_id = co.manager_id
                    WHERE co.manager_id IN(SELECT manager_id FROM manager_bind_history WHERE manager_id=m.id) AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time AND date_format(co.payment_date,'%Y-%m') = '$date') order_count,
                    (SELECT IFNULL(SUM(order_money),0)  FROM customer_order co 
                    LEFT JOIN manager_bind_history mbh ON mbh.manager_id = co.manager_id
                    WHERE co.manager_id IN(SELECT manager_id FROM manager_bind_history WHERE manager_id=m.id) AND co.payment_date is not null AND  co.order_date BETWEEN mbh.start_time AND mbh.end_time AND date_format(co.payment_date,'%Y-%m') = '$date') order_total_money,
                    (SELECT COUNT(1) FROM manager_bind_history  WHERE parent_id=m.id AND date_format(start_time,'%Y-%m') = '$date') recruit_count, 
                      (SELECT COUNT(1)
                        FROM customer
                          WHERE customer.manager_id = m.id AND date_format(created_at, '%Y-%m') = '$date') customer_count
                FROM manager  m
                WHERE m.id = $manager_id
        ";
        // echo '<pre/>';print_r($rs);exit;
        $rs = $this->db->query($sql)->result_array();
        if ($rs){
            return $rs;
        }else{
            return false;
        }
    }
    
}